﻿using Cassandra;
using IDAdmin.Lib.Utils;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;

namespace IDAdmin.Lib.DataLayer
{
    public class LevelWebDB
    {
        volatile private bool stop;
        public string tableName;
        public List<DateTime> dateTimeList;

        public void continueTransfer()
        {
            stop = false;
        }
        public void stopTransfer()
        {
            stop = true;
        }
        /// <summary>
        /// Lấy dữ liệu au
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public string getLevel(string charid, string fromDateStr, string toDateStr)
        {
            string level = "";
            List<LevelDTO> levelList = new List<LevelDTO>();
            try
            {
                using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                {
                    string cmdText = "select level,datetime from levelvn03 where charid={0} and datetime>='{1}' and datetime<='{2}'";
                    cmdText = string.Format(cmdText, charid, fromDateStr, toDateStr) + " ALLOW FILTERING";
                    RowSet rowSet = session.Execute(cmdText);
                    foreach (Row row in rowSet)
                    {
                        LevelDTO levelDto = new LevelDTO();
                        levelDto.Level = row["level"].ToString();
                        levelDto.Datetime = row.GetValue<DateTime>(1);
                        levelList.Add(levelDto);
                    }
                }
                if (levelList != null && levelList.Count > 0)
                {
                    level = levelList.OrderByDescending(o => o.Datetime).FirstOrDefault().Level;
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return level;
        }
        public void work()
        {
            transfer_Level_Table(this.tableName, this.dateTimeList);
        }

        public void countRowNum()
        {
            using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
            {

                string cmdText = "";
                cmdText = "select charid from {0} where temp=0 and datetime >= '{1}' and datetime<= '{2}' ALLOW FILTERING";
                cmdText = string.Format(cmdText, tableName, dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00", dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59");
                RowSet rowset = session.Execute(cmdText);
                int numRow = rowset.GetRows().Count();
            }
        }
        public void transfer_Level_Table(string tablename, List<DateTime> dateTimeList)
        {
            try
            {
                RowSet rowset;
                using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                {

                    string cmdText = "";
                    cmdText = "select charid,datetime,level from {0} where temp=0 and datetime >= '{1}' and datetime<= '{2}' ALLOW FILTERING";
                    cmdText = string.Format(cmdText, tablename, dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00", dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59");
                    rowset = session.Execute(cmdText,1000000000);
                    //int rowNum=rowset.GetRows().Count();
                }
                //using (SqlConnection conn = ConnectionHelper.GetConnection())
                //{
                //    SqlCommand sqlcommand = new SqlCommand();
                //    sqlcommand.CommandTimeout = 3600;
                //    sqlcommand.Connection = conn;
                //    sqlcommand.CommandType = CommandType.StoredProcedure;
                //    sqlcommand.CommandText = "spTransfer_delete_Level";
                //    sqlcommand.Parameters.AddWithValue("@tablename", tablename);
                //    sqlcommand.Parameters.AddWithValue("@fromDT", dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00");
                //    sqlcommand.Parameters.AddWithValue("@toDT", dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59");
                //    int affRow = sqlcommand.ExecuteNonQuery();
                //}
                //List<Row> rList=rowset.GetRows().ToList();
                foreach (Row r in rowset)
                {
                    if (stop == false)
                    {
                        try
                        {
                            using (SqlConnection conn = ConnectionHelper.GetConnection())
                            {
                                SqlCommand command = new SqlCommand();
                                command.Connection = conn;
                                command.CommandType = CommandType.StoredProcedure;
                                command.CommandText = "spTransfer_Insert_Level";
                                command.Parameters.AddWithValue("@charid", r["charid"].ToString());
                                command.Parameters.AddWithValue("@datetime", r.GetValue<DateTime>("datetime").ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss"));
                                command.Parameters.AddWithValue("@level", r["level"].ToString());
                                command.Parameters.AddWithValue("@md5", CryptHelper.EncodeMD5(r["charid"].ToString() + "," + r.GetValue<DateTime>("datetime").ToLocalTime().ToString("yyyy-MM-dd HH:mm:ss") + "," + r["level"].ToString()));
                                command.Parameters.AddWithValue("@tablename", tablename);
                                SqlParameter outputParam = command.Parameters.Add("@Result", SqlDbType.Int);
                                outputParam.Direction = ParameterDirection.Output;
                                command.ExecuteNonQuery();
                            }
                        }
                        catch (Exception ex)
                        {
                            string ms = ex.Message;
                        }
                    }
                    else
                    {
                        break;
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static void delete_Lau_Table(string fromDT, string toDT)
        {
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                SqlCommand sqlcommand = new SqlCommand();
                sqlcommand.CommandTimeout = 3600;
                sqlcommand.Connection = conn;
                sqlcommand.CommandType = CommandType.StoredProcedure;
                sqlcommand.CommandText = "spTransfer_delete_Lau";
                sqlcommand.Parameters.AddWithValue("@fromDT", fromDT);
                sqlcommand.Parameters.AddWithValue("@toDT", toDT);
                int affRow = sqlcommand.ExecuteNonQuery();
            }
        }
        public static void delete_CALIN_Table(string fromDT, string toDT)
        {
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                SqlCommand sqlcommand = new SqlCommand();
                sqlcommand.CommandTimeout = 3600;
                sqlcommand.Connection = conn;
                sqlcommand.CommandType = CommandType.StoredProcedure;
                sqlcommand.CommandText = "sp_delete_Calin";
                sqlcommand.Parameters.AddWithValue("@fromDT", fromDT);
                sqlcommand.Parameters.AddWithValue("@toDT", toDT);
                int affRow = sqlcommand.ExecuteNonQuery();
            }
        }
        public static void delete_A7_Table(string fromDT, string toDT)
        {
            using (SqlConnection conn = ConnectionHelper.GetConnection())
            {
                SqlCommand sqlcommand = new SqlCommand();
                sqlcommand.CommandTimeout = 3600;
                sqlcommand.Connection = conn;
                sqlcommand.CommandType = CommandType.StoredProcedure;
                sqlcommand.CommandText = "spTransfer_delete_A7";
                sqlcommand.Parameters.AddWithValue("@fromDT", fromDT);
                sqlcommand.Parameters.AddWithValue("@toDT", toDT);
                int affRow = sqlcommand.ExecuteNonQuery();
            }
        }
        public static void insert_Lau_Table(string tablename, DateTime datetime, string account, string charid, string gamezone)
        {
            try
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = conn;
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "spTransfer_Insert_Lau";
                    command.Parameters.AddWithValue("@datetime", datetime);
                    command.Parameters.AddWithValue("@account", account);
                    command.Parameters.AddWithValue("@charid", charid);
                    command.Parameters.AddWithValue("@gamezone", gamezone);
                    command.Parameters.AddWithValue("@tablename", tablename);
                    SqlParameter outputParam = command.Parameters.Add("@Result", SqlDbType.Int);
                    outputParam.Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
            }
        }
        public static void insert_CALIN_Table(DateTime datetime, string account, string charid, string gamezone)
        {
            try
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = conn;
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "sp_Insert_CALIN";
                    command.Parameters.AddWithValue("@datetime", datetime);
                    command.Parameters.AddWithValue("@account", account);
                    command.Parameters.AddWithValue("@charid", charid);
                    command.Parameters.AddWithValue("@gamezone", gamezone);
                    SqlParameter outputParam = command.Parameters.Add("@Result", SqlDbType.Int);
                    outputParam.Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {

            }
        }
        public static void insert_A7_Table(string tablename, DateTime datetime, string account, string charid, string gamezone)
        {
            try
            {
                using (SqlConnection conn = ConnectionHelper.GetConnection())
                {
                    SqlCommand command = new SqlCommand();
                    command.Connection = conn;
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "spTransfer_Insert_A7";
                    command.Parameters.AddWithValue("@datetime", datetime);
                    command.Parameters.AddWithValue("@account", account);
                    command.Parameters.AddWithValue("@charid", charid);
                    command.Parameters.AddWithValue("@gamezone", gamezone);
                    command.Parameters.AddWithValue("@tablename", tablename);
                    SqlParameter outputParam = command.Parameters.Add("@Result", SqlDbType.Int);
                    outputParam.Direction = ParameterDirection.Output;
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {

            }
        }
    }
}
